using System.Collections;
using System.Text;
using PIM.Utilities.DataAccess;
using System;

namespace PIM.Utilities
{
	public abstract class QSQueryBuilder
	{
		protected DBProvider _dbProviderType;
		protected string _tableName;
		protected StringBuilder _colNames;
		protected StringBuilder _colValues;
		private Hashtable _clobObjects;

		public QSQueryBuilder(DBProvider dbProviderType, string tableName)
		{
			_dbProviderType = dbProviderType;
			_tableName = tableName;
			_colNames = new StringBuilder();
			_colValues = new StringBuilder();
			_clobObjects = new Hashtable();
		}

		public Hashtable Params
		{
			get { return _clobObjects; }
		}

		public abstract string GetQueryText(string whereClause);

		public void RemoveAll()
		{
			_colNames.Length = 0;
			_colValues.Length = 0;
			Params.Clear();
		}

        public string TableName
        {
            get
            {
                return _tableName;
            }
        }
	}

	public class QSQueryBuilderInsert : QSQueryBuilder
	{
		public QSQueryBuilderInsert(DBProvider dbProviderType, string tableName) : base(dbProviderType, tableName)
		{
		}

		public override string GetQueryText(string whereClause)
		{
			return string.Format("INSERT INTO {0} ({1}) VALUES ({2})", _tableName, _colNames.ToString().Remove(_colNames.Length-1, 1), _colValues.ToString().Remove(_colValues.Length-1, 1));
		}

		public void AddColumnData(string colName, object colValue, DATATYPE colType)
		{
			AddColumnData(colName, colValue, colType, null);
		}

		public void AddColumnData(string colName, object colValue, DATATYPE colType, object colValueIfNull)
		{
			_colNames.AppendFormat("{0},", colName);

			if(colValue == null || colValue.ToString() == string.Empty)
				colValue = colValueIfNull;

			if (colValue == null)
			{
				_colValues.Append("NULL,");
			}
			else
			{
				switch(colType)
				{
					case DATATYPE.IMAGE :
						_colValues.AppendFormat("@{0},", colName);
						Params.Add(colName, new NTextString(colValue.ToString()));
						break;
					case DATATYPE.DATETIME :
					case DATATYPE.DATE :
					case DATATYPE.TIME :
						DateTime dt = QSecureHelper.ConvertStringToDateTime(colValue.ToString(),false);
						_colValues.AppendFormat("@{0},",colName );
						Params.Add(colName, dt);
						break;
					default:
						_colValues.AppendFormat("@{0},",colName );
						Params.Add(colName, colValue.ToString());
						break;
				}
			}
		}
	}


	public class QSQueryBuilderUpdate : QSQueryBuilder
	{
		public QSQueryBuilderUpdate(DBProvider dbProviderType, string tableName) : base(dbProviderType, tableName)
		{
		}

		public override string GetQueryText(string whereClause)
		{
			if (whereClause == null || whereClause.Length == 0)
				return string.Format("UPDATE {0} SET {1}", _tableName, _colNames.ToString().Remove(_colNames.Length-1, 1));
			else
				return string.Format("UPDATE {0} SET {1} WHERE {2}", _tableName, _colNames.ToString().Remove(_colNames.Length-1, 1), whereClause);
		}

		
		public void AddColumnData(string colName, object colValue, DATATYPE colType)
		{
			AddColumnData(colName, colValue, colType, null);
		}

		public void AddColumnData(string colName, object colValue, DATATYPE colType, object colValueIfNull)
		{
			if(colValue == null || colValue.ToString() == string.Empty)
			{
				if (colValueIfNull != null)
					_colNames.AppendFormat("{0}={1},", colName, colValueIfNull.ToString());
				else
					_colNames.AppendFormat("{0}=NULL,", colName);
			}
			else
			{
				switch(colType)
				{
					case DATATYPE.IMAGE :
						_colNames.AppendFormat("{0}=@{0},", colName);
						Params.Add(colName, new NTextString(colValue.ToString()));
						break;
					case DATATYPE.DATETIME :
						_colNames.AppendFormat("{0}=@{0},", colName );
						DateTime dt = QSecureHelper.ConvertStringToDateTime(colValue.ToString(),false);
						Params.Add(colName, dt);
						break;
					default:
						_colNames.AppendFormat("{0}=@{0},", colName);
						Params.Add(colName, colValue.ToString());
						break;
				}
			}
		}

	}

	public class QSQueryBuilderSelect : QSQueryBuilder
	{
		public QSQueryBuilderSelect(string tableName) : base(DBProvider.Null, tableName)
		{
		}

		public override string GetQueryText(string whereClause)
		{
			if (whereClause == null || whereClause.Length == 0)
				return string.Format("SELECT {1} FROM {0}", _tableName, _colNames.ToString().Remove(_colNames.Length-1, 1));
			else
				return string.Format("SELECT {1} FROM {0}  WHERE {2}", _tableName, _colNames.ToString().Remove(_colNames.Length-1, 1), whereClause);
		}

		public void AddColumnData(string colName)
		{
			if(colName != null && colName.Length != 0)
				_colNames.AppendFormat("{0},", colName);
		}
	}
}